#!/usr/bin/python
# -*- coding:utf-8 -*-

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='root', db='db_spider', charset="utf8")
cursor = conn.cursor()
select_sql = "SELECT `name`, COUNT(zhihu_question.question_id) / (SELECT count(zhihu_question.question_id) FROM zhihu_question) AS '权重' FROM zhihu_question JOIN zhihu_question_topic ON zhihu_question.question_id = zhihu_question_topic.question_id JOIN zhihu_topic ON zhihu_question_topic.topic_id = zhihu_topic.topic_id GROUP BY zhihu_topic.`name` ORDER BY COUNT(zhihu_question.question_id) DESC"
cursor.execute(select_sql)
results = cursor.fetchall()
with open('output.json', 'w') as f:
    f.write('{\n')
    f.write('\t"children":[\n')
    for row in results:
        f.write(
            '\t\t{"name":"%s","weight":%f},\n    ' % (row[0].replace('\n', '').replace('\"', '\\\"').encode('utf-8'), row[1]))
    f.write('\t]\n')
    f.write('}\n')
